﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


namespace Extractor.LibCode
{
    public class cDBService
    {
        #region Property
        private cAppDal dbserver;
        private string _tablename;
        private string _msg;
        private string sSQL;
        private bool _noerror = true;

        public bool NoError
        {
            get { return _noerror; }
            set { _noerror = value; }
        }

        private string _dbname;

        public string DbName
        {
            get { return dbserver.cnfg.Dbname; }
            // set { _dbname = value; }
        }

        private string _serverprovider;
        public string ServerProvider
        {
            get { return dbserver.cnfg.Provider; }

        }

        protected cAppDal Dbserver
        {
            get { return dbserver; }
            set { dbserver = value; }
        }
        public string Msg
        {
            get { return _msg; }
            set { _msg = value; }
        }
        public string Tablename
        {
            get { return _tablename; }
            set { _tablename = value; }
        }
        #endregion


        public cDBService(string sProvider)
        {
            if (sProvider.ToLower() == "sqlserver")
            {
                Dbserver = new cSSDAL();
            }
            else if (sProvider.ToLower() == "mysql")
            {
                //   dbserver = new cMYDAL();
            }
            else
            {
                throw new Exception("Invalid Provider");
            }

        }

        public cDBService()
        {

            cBaseConfig oConfig = new cBaseConfig();


            if (oConfig.Provider.ToString().ToLower() == "sqlserver")
            {
                Dbserver = new cSSDAL();
            }
            else
            {
                //  dbserver = new cMYDAL();
            }

        }


        #region Logging

        //public void onError(Exception ex, object ob)
        //{

        //    errorLog(userID, ob.GetType().ToString(), "", "", ex);
        //    throw ex;
        //}
        //public void onError(Exception ex, object ob, bool bproperty)
        //{
        //    if (bproperty)
        //    {
        //       // errorLog(LoginName, ob.GetType().ToString(), Sesmenu, Sesrrx, ex);
        //    }
        //    else
        //    {
        //        onError(ex, ob);
        //    }
        //    throw ex;
        //}

        //public void onError(Exception ex, object ob, string smenu, string trx)
        //{
        //    errorLog(LoginName, ob.GetType().ToString(), smenu, trx, ex);
        //    throw ex;
        //}
        //private void errorLog(string suid, string sclass, string smenu, string trx, Exception ex)
        //{
        //    string sql = string.Format("insert into sy_log values('{0}','{1}','{2}','{3}',now(),'{4}')",
        //                            suid, sclass, smenu, trx, ex.ToString().Replace("'", "''"));
        //    if (dbserver.openConn())
        //    {
        //        dbserver.ExecuteNonQuery(sql);
        //    }
        //}

        #endregion\

        #region CRUD Method
        public DataTable getTableStructure(string tablename)
        {
            try
            {
                DataTable dt = new DataTable();
                string s = "";
                 getTableStructure(ref dt, tablename, out s);
                 return dt;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        
        public bool getTableStructure(ref DataTable dt, string tablename)
        {
            try
            {
                string s = "";
                return getTableStructure(ref dt, tablename, out s);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public bool getTableStructure(ref DataTable dt, string tablename, out string msg)
        {
            try
            {
                dt = new DataTable();
                msg = "";
                if (ServerProvider == "MYSQL")
                {
                    sSQL = "select * from " + tablename + " limit 0";
                }
                else
                {
                    sSQL = "select top 0 * from " + tablename;
                }
                dt = dbserver.ExecuteQuery(sSQL, tablename);
                msg = "Success";
                return true;
            }
            catch (Exception ex)
            {

                throw ex;
            }


        }

        public DateTime getServerTime()
        {
            try
            {
                return (DateTime)dbserver.ExecuteScalar("select getdate()");
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public virtual bool AddNewRowDB(out string msg1, int[] indexgenfieldname,
            string[] genid, bool haveHistory, params object[] fldValue)
        {
            return AddNewRowDB(out msg1, Tablename, indexgenfieldname, genid, haveHistory, fldValue);
        }



        public virtual bool AddNewRowDB(out string msg1, string tablename,
           int[] indexgenfieldname, string[] genid, bool haveHistory, params object[] fldValue)
        {
            try
            {
                msg1 = "";
                if (indexgenfieldname.Length != genid.Length)
                {
                    msg1 = "Length of Indexgenfield not equal with srting genid length";
                    return false;
                }
                if (ServerProvider.ToUpper() == "MYSQL")
                {
                    sSQL = "select * from " + tablename + " limit 0";
                }
                else
                {
                    sSQL = "select top 0 * from " + tablename;
                }
                DataTable dt = new DataTable();
                dt = dbserver.ExecuteQuery(sSQL, tablename);

                DataRow dr = dt.NewRow();
                int i = 0;
                int j;
                foreach (int k in indexgenfieldname)
                {
                    fldValue[k] = new cDBHelper().genIDCommit(genid[k]);
                }
                //                fldValue[0] = new cDBHelper().genIDCommit("GENKDBRG");
                foreach (DataColumn dc in dt.Columns)
                {
                    if (fldValue[i].ToString().ToUpper() != "[SKIP]")
                    {
                        dr[dc.ColumnName] = fldValue[i];


                    }
                    i++;
                    if (i == fldValue.Count())
                        break;
                }

                if (haveHistory)
                {
                    //  dr["idcreated"] = new cApplSession().userID;
                    dr["dtcreated"] = getServerTime();
                    dr["dtupdated"] = dr["dtcreated"];
                }

                dt.Rows.Add(dr);
                if (dbserver.openConn())
                {
                    j = dbserver.SqlUpdate(dt, tablename, out msg1);

                    if (j == 1)
                    {
                        msg1 = "Sukses";
                        Msg = msg1;
                        Dbserver.CommitAndCloseConn();
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    msg1 = "Database unreachable";
                    Msg = msg1;
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        private string genWhere(string[] keyfieldname, object[] val)
        {
            try
            {
                string sql = "";
                int i = 0;
                foreach (string s in keyfieldname)
                {
                    sql += "and " + s + "=";
                    if (val[i].GetType().ToString() == "System.String" || val[i].GetType().ToString() == "System.DateTime")
                    {
                        sql += "'" + val[i].ToString() + "' ";
                    }
                    else
                    {
                        sql += val[i].ToString() + " ";
                    }
                    i++;
                }
                return sql;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public virtual bool ExecuteUpdate(DataTable dt, out string sout)
        {
            try
            {
                if (dbserver.openConn())
                {
                    sout = "";
                    int iret = dbserver.SqlUpdate(dt, dt.TableName, out sout);
                    if (dbserver.StateConn == ConnectionState.Open)
                    {
                        dbserver.CommitAndCloseConn();
                    }
                    return iret > 0;
                }
                else
                {

                    sout = "Connection Refused";
                    return false;
                }

            }
            catch (Exception ex)
            {
                sout = "";

            }
            finally
            {
                if (dbserver.StateConn == ConnectionState.Open)
                {
                    dbserver.CommitAndCloseConn();
                }

            }
            return true;
        }
        //public virtual bool getComboboxdata(string tablename, string stext, string sval, string where, ref DataTable dt)
        //{
        //    try
        //    {
        //        if (where != "")
        //            where = "Where " + where;
        //        dt = new DataTable();
        //        string sql = "select " + stext + "," + sval + " from " + tablename + where;
        //        dt = Dbserver.ExecuteQuery(sql, tablename);
        //        return dt.Rows.Count > 0;

        //    }
        //    catch (Exception ex)
        //    {

        //        throw ex;
        //    }

        //}
        public virtual bool saveimage(string imgid, byte[] img)
        {
            try
            {
                DataTable dt = new DataTable();
                //img.RawFormat
                getTableStructure(ref dt, "tb_imgpool", out _msg);
                DataRow dr = dt.NewRow();
                dr["imgid"] = imgid;


                //byte[] bt = new cCommon().getBinaryImage(img);
                dr["imgdata"] = img;
                dt.Rows.Add(dr);
                dbserver.SqlUpdate(dt, "tb_imgpool", out _msg);
                return true;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public virtual bool saveimage(byte[] img, out string sid)
        {
            try
            {
                // string sid="";
                sid = new cDBHelper().genIDCommit("GENIMGID");
                return saveimage(sid, img);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region nanti kalo butuh
        public bool getInquirySQL(ref DataTable dt, string sql, string tbname, out string msg)
        {
            try
            {
                dt = new DataTable();
                dt = dbserver.ExecuteQuery(sql, tbname);
                msg = "";
                return dt.Rows.Count > 0;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool getInquiryAllPartsModul(ref DataTable dt, string matname, string wrhid, string modulid)
        {
            try
            {
                string sql = "select * from vw_allparts_modul_v  where 1=1 and itnm like '%" + matname + "%' and chitno like '%" + matname + "%' and wrhid='" + wrhid + "' and paitmfbp='" + modulid + "'";
                dt = new DataTable();
                dt = dbserver.ExecuteQuery(sql, "vw_allparts_modul_v");
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public bool getComboboxData(ref DataTable dt, string tablenam, string valuemember, string textmember, DICTIOARY_TYPE dtyp)
        {
            //      dbserver.openConn();
            string sql = "";
            if (dtyp!=DICTIOARY_TYPE.NONE)
            {
                 sql = "select " + valuemember + " as svalue," + textmember + " from tbdictionary where codetype='"+cCommon.DictionaryType(dtyp)+"'";
            }
            else
            {

                 sql = "select " + valuemember + " as svalue," + textmember + " from " + tablenam  +" order by svalue";
            }

            dt = new DataTable();
            dt = dbserver.ExecuteQuery(sql, tablenam);
            //    dbserver.CommitAndCloseConn();
            return dt.Rows.Count > 0;
        }


        public virtual bool getInquiry(ref DataTable dt, string[] obVal, string inqTable, out string msg)
        {
            try
            {
                msg = "";



                DataTable dts = new DataTable();
                getTableStructure(ref dts, inqTable, out msg);
                sSQL = "select * from " + inqTable + " where 1=1 ";
                int i = 0;
                string stemp = "";
                foreach (object ob in obVal)
                {
                    if (ob.GetType().ToString() == "System.String")
                    {
                        stemp = ob.ToString().Trim();
                        if (stemp != string.Empty)
                        {
                            if (stemp.Substring(0, 1) == "%" && stemp.Length > 2)
                            {
                                sSQL += "and " + dts.Columns[i].ColumnName + " like '" + stemp + "'";
                            }
                            else
                            {
                                if (stemp.Substring(0, 1) != "'" && stemp.Length > 2)
                                {
                                    sSQL += "and " + dts.Columns[i].ColumnName + "='" + stemp + "'";
                                }
                                else
                                {
                                    sSQL += "and " + dts.Columns[i].ColumnName + "=" + stemp;
                                }
                            }

                        }

                    }
                    else
                    {
                        if (ob != null)
                        {
                            sSQL += "and " + dts.Columns[i].ColumnName + "=" + ob.ToString();
                        }
                    }
                    i++;
                }
                dt = Dbserver.ExecuteQuery(sSQL, inqTable);
                return dt.Rows.Count > 0;

            }
            catch (Exception ex)
            {

                throw ex;
            }
            return true;
        }



        #endregion

        public virtual bool isRecordExist(string tablename, string[] keyfieldname, object[] keyvalue)
        {
            try
            {

                DataTable dt = new DataTable();

                sSQL = "select * from " + tablename + " where 1=1  " + genWhere(keyfieldname, keyvalue);
                dt = Dbserver.ExecuteQuery(sSQL, tablename);
                return (dt.Rows.Count > 0);

            }
            catch (Exception ex)
            {

                throw;
            }
        }

        public virtual bool EditRowDtDB(out string msg1, string[] keyfieldname, object[] keyvalue, bool haveHistory, params object[] fldValue)
        {
            return EditRowDtDB(out msg1, Tablename, keyfieldname, keyvalue, haveHistory, fldValue);

        }
        public virtual bool EditRowDtDB(out string msg1, string tablename, string[] keyfieldname, object[] keyvalue, bool haveHistory, params object[] fldValue)
        {
            try
            {
                msg1 = "";
                if (keyfieldname.Length != keyvalue.Length)
                {
                    msg1 = "Field Definition length not equal with value member length";
                    return false;
                }

                DataTable dt = new DataTable();
                // inqTableName = "tb_transstudio";
                sSQL = "select * from " + tablename + " where 1=1  " + genWhere(keyfieldname, keyvalue);
                dt = Dbserver.ExecuteQuery(sSQL, tablename);
                if (dt.Rows.Count > 0)
                {
                    //  inqTableName = "vw_booking";
                    DataRow dr = dt.Rows[0];
                    int i = 0;
                    int j = 0;
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (fldValue[i].ToString().ToUpper() != "[SKIP]")
                        {
                            dr[dc.ColumnName] = fldValue[i];
                        }
                        i++;
                        if (i == fldValue.Count())
                            break;
                    }
                    if (haveHistory)
                    {
                        // dr["idupdated"] = new cApplSession().userID;
                        dr["dtupdated"] = getServerTime();
                    }
                    if (Dbserver.openConn())
                    {

                        j = Dbserver.SqlUpdate(dt, tablename, out msg1);
                        if (j == 1)
                        {
                            msg1 = "Records Update Success. ";
                            Dbserver.CommitAndCloseConn();
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                    msg1 = "Update Success";
                    return true;
                }
                else
                {
                    //    inqTableName = "vw_booking";
                    msg1 = "Records not Found";
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public virtual bool deleteRowDB(string[] keyfieldname, object[] keyvalue, out string msg1)
        {
            return deleteRowDB(keyfieldname, keyvalue, Tablename, out msg1);
        }
        public virtual bool deleteRowDBSQL(string[] keyfieldname, object[] keyvalue, string tablename, out string msg1)
        {
            try
            {
                int nret = 0;
                msg1 = "";
                if (keyfieldname.Length != keyvalue.Length)
                {
                    msg1 = "Field Definition length not equal with value member length";
                    return false;
                }
                DataTable dt = new DataTable();
                // inqTableName = "tb_transstudio";
                sSQL = "select * from " + tablename + " where 1=1 " + genWhere(keyfieldname, keyvalue);
                dt = Dbserver.ExecuteQuery(sSQL, tablename);
                if (dt.Rows.Count > 0)
                {
                    //  inqTableName = "vw_booking";
                    if (Dbserver.openConn())
                    {
                        sSQL = "delete " + tablename + " where 1=1 " + genWhere(keyfieldname, keyvalue);

                        //nret = Dbserver.SqlUpdate(dt, tablename, out msg1);
                        dbserver.ExecuteNonQuery(sSQL);

                        //nret=1=success else error
                        //dt.AcceptChanges();
                        nret = 1;
                        Dbserver.CommitAndCloseConn();
                    }
                    msg1 = "Delete Success";
                    return true;
                }
                else
                {
                    //    inqTableName = "vw_booking";
                    msg1 = "Records not Found";
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public virtual bool deleteRowDB(string[] keyfieldname, object[] keyvalue, string tablename, out string msg1)
        {
            try
            {
                int nret = 0;
                msg1 = "";
                if (keyfieldname.Length != keyvalue.Length)
                {
                    msg1 = "Field Definition length not equal with value member length";
                    return false;
                }
                DataTable dt = new DataTable();
                // inqTableName = "tb_transstudio";
                sSQL = "select * from " + tablename + " where 1=1 " + genWhere(keyfieldname, keyvalue);
                dt = Dbserver.ExecuteQuery(sSQL, tablename);
                if (dt.Rows.Count > 0)
                {
                    //  inqTableName = "vw_booking";
                    if (Dbserver.openConn())
                    {
                        dt.Rows[0].Delete();
                        nret = Dbserver.SqlUpdate(dt, tablename, out msg1);
                        //nret=1=success else error
                        //dt.AcceptChanges();
                        Dbserver.CommitAndCloseConn();
                    }
                    msg1 = "Delete Success";
                    return true;
                }
                else
                {
                    //    inqTableName = "vw_booking";
                    msg1 = "Records not Found";
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        #endregion

        //public virtual bool getComboboxdata(string tablename, string stext, string sval, ref DataTable dt)
        //{
        //    try
        //    {
        //        dt = new DataTable();
        //        string sql = "select " + stext + "," + sval + " from " + tablename;
        //        dt = Dbserver.ExecuteQuery(sql, tablename);
        //        return dt.Rows.Count > 0;

        //    }
        //    catch (Exception ex)
        //    {

        //        throw ex;
        //    }

        //}
    }
}
